package com.ea.eadp.nexus.test.common;

import oracle.jdbc.pool.OracleDataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseHelper {
    private static DatabaseHelper instance;
    private Connection connection;
    private static final Log log = LogFactory.getLog(DatabaseHelper.class);
    private Statement statement;

    private DatabaseHelper() throws SQLException, ClassNotFoundException {
        String dbUrl = SystemProperties.instance().getProperty("identity.db.url");
        String dbUserName = SystemProperties.instance().getProperty("identity.db.usr");
        String dbPassword = SystemProperties.instance().getProperty("identity.db.pwd");

        Class.forName("oracle.jdbc.driver.OracleDriver");
        OracleDataSource dataSource = new OracleDataSource();
        dataSource.setURL(dbUrl);
        dataSource.setUser(dbUserName);
        dataSource.setPassword(dbPassword);
        connection = dataSource.getConnection();
        connection.setAutoCommit(true);
    }

    public static synchronized DatabaseHelper instance() throws SQLException, ClassNotFoundException {
        if (instance == null)
            instance = new DatabaseHelper();
        return instance;
    }

    public synchronized int executeUpdate(String query) throws SQLException {
        int updateCount = -1;
        try {
            statement = connection.createStatement();
            log.info(String.format("Executing update: %s", query));
            updateCount = statement.executeUpdate(query);
        } finally {
            cleanUp();
        }
        return updateCount;
    }

    public synchronized Object querySingleColumn(String query) throws SQLException {
        Object value = null;
        try {
            ResultSet result = executeQuery(query);
            if (result != null) {
                value = result.getObject(1);
                log.info(String.format("Received: '%s'", value));
            }
        } finally {
            cleanUp();
        }
        return value;
    }

    private ResultSet executeQuery(String query) throws SQLException {
        ResultSet result = null;
        statement = connection.createStatement();
        log.info(String.format("Executing query: %s", query));
        result = statement.executeQuery(query);
        if (!result.next())
            return null;
        return result;
    }

    private void cleanUp() throws SQLException {
        if (statement != null) {
            statement.close();
            statement = null;
        }
    }
}